package com.five.wisdomClass.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.five.wisdomClass.entity.*;
import org.apache.ibatis.annotations.*;

import java.util.List;


@Mapper
public interface StudentMapper extends BaseMapper<Student> {

    // 通过专业班级查询学生的姓名，学号，电话
    @Select("SELECT s_number,s_name,s_phone,class AS classname FROM smartcampus.studentinfo WHERE class = #{classname};")
    List<Student> getClassMates(@Param("classname") String classname);

    // 查看院系，专业，班级，个人信息等
    @Select("SELECT s_number,s_name,academy,major,class AS classname FROM smartcampus.studentinfo WHERE s_number = #{sNumber};")
    Student getSelfInfo(@Param("sNumber") String sNumber);

    // 查看公共课信息:
    // 通过学生信息表的班级名，查询班级表中的班级号，去关系表中查班级号对应的公共课程号
    // 最后用公共课程号去课程表中查询课程信息
    @Select("SELECT class_id FROM smartcampus.specialized_class WHERE class = #{classname};")
    String getClassId(@Param("classname") String classname);
    @Select("SELECT course_number FROM smartcampus.class_public_course WHERE class_id = #{classId}")
    List<String> getPublicCourseNumber(@Param("classId") String classId);
    @Select("SELECT course,credits,teacher_name,start_time,end_time,location FROM smartcampus.public_course WHERE course_number = #{courseNumber}")
    Course getPublicCourse(@Param("courseNumber") String courseNumber);

    // 查看选修课信息：
    // 通过学生学号查询关系表中的课程号，再通过课程号查询课程表中的课程信息
    @Select("SELECT course_number FROM smartcampus.student_take_course WHERE s_number = #{sNumber}")
    List<String> getTakeCourseNumbers(@Param("sNumber") String sNumber);
    @Select("Select course,credits,teacher_name,start_time,end_time,location FROM smartcampus.take_course WHERE course_number = #{courseNumber}")
    Course getTakeCourse(@Param("courseNumber") String courseNumber);

    // 查询课程成绩
    @Select("SELECT s_name,s_number,course,grades,credits FROM smartcampus.scores WHERE s_number = #{sNumber}")
    List<Scores> getScores(@Param("sNumber") String sNumber);

    // 添加个人计划
    @Insert("INSERT INTO smartcampus.self_plan VALUES(#{sNumber},current_time,#{context},#{id})")
    int addPlan(SelfPlan selfPlan);

    // 删除个人计划
    @Delete("DELETE FROM smartcampus.self_plan WHERE id = #{id}")
    int deletePlan(Integer id);

    // 编辑个人计划
    @Update("UPDATE smartcampus.self_plan SET context = #{context} WHERE id = #{id}")
    int editPlan(String context,Integer id);

    // 查看个人计划
    @Select("SELECT context,`date` FROM smartcampus.self_plan WHERE s_number = #{sNumber}")
    List<SelfPlan> showPlan(@Param("sNumber") String sNumber);

    // 请假
    @Insert("INSERT INTO smartcampus.leave(s_number,s_name,leave_type,start_time,end_time,contact,context) VALUES(#{sNumber},#{sName},#{leaveType},#{startTime},#{endTime},#{contact},#{context})")
    int submitLeave(Leave leave);

}
